package org.myframework.jdbc;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.myframework.jdbc.cache.Cache;
import org.myframework.jdbc.cache.impl.EhcacheCache;
import org.myframework.jdbc.dialect.Dialect;
import org.myframework.jdbc.dialect.impl.OracleDialect;
import org.myframework.jdbc.sqlconfig.SqlMapper;
import org.myframework.jdbc.sqltemplate.SqlTemplate;
import org.myframework.jdbc.sqltemplate.impl.VelocitySqlTemplate;
import org.myframework.util.ResultMap;
import org.myframework.util.StringUtil;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterUtils;
import org.springframework.jdbc.core.namedparam.ParsedSql;

public class MyNamedParameterJdbcTemplate extends NamedParameterJdbcTemplate{
	private static Log log = LogFactory.getLog(MyNamedParameterJdbcTemplate.class);
	
	public MyNamedParameterJdbcTemplate(JdbcOperations classicJdbcTemplate) {
		super(classicJdbcTemplate);
	}
	
	private static int DEFAULT_PAGE_SIZE = 50;

	protected Cache dataCache = new EhcacheCache(getClass().getName()); 

	private SqlTemplate sqlTemplate = new VelocitySqlTemplate(); 

	private Dialect dialect = new OracleDialect(); 
	
	/**
	 * 
	 * @param mapperId
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> selectAllList(String sqlKey,
			Map<String, Object> map) {
		String cacheKey = getCacheKey(sqlKey, map);
		SqlMapper sqlMapper = sqlTemplate.getSqlMapper(sqlKey);
		String sql = sqlTemplate.getSql(sqlKey, map);
		if (sqlMapper == null) {
			throw new RuntimeException("load sqlconfig error :" + sqlKey);
		}
		if (sqlMapper.isFlushCache()) {
			dataCache.removeObject(cacheKey);
		}
		if (sqlMapper.isUseCache()) {
			if (dataCache.getObject(cacheKey) != null) {
				log.debug("load from cacheKey : " + cacheKey);
				return (List<Map<String, Object>>) dataCache
						.getObject(cacheKey);
			} else {
				log.debug("reload   cacheKey : " + cacheKey);
				List<Map<String, Object>> rs = this.queryForList(sql, map);
				dataCache.putObject(cacheKey, rs);
				return rs;
			}
		} else {
			List<Map<String, Object>> rs = this.queryForList(sql, map);
			return rs;
		}
	}

	/**
	 * @param sqlKey
	 * @param map
	 * @param offset
	 * @param limit
	 * @return   offset+1~offset+limit 
	 */
	public List<Map<String, Object>> selectPageList(String sqlKey,
			Map<String, Object> map, int offset, int limit) {
		log.debug("offset " + offset + " limit " + limit);
		long startTime = System.currentTimeMillis();
		String sql = sqlTemplate.getSql(sqlKey, map);
		if (dialect!=null){
			sql = dialect.getPageSql(sql, offset, limit, map);
			long endTime = System.currentTimeMillis();
			log.debug("getSql costTime ms :" + (endTime - startTime));
			log.debug(sql);
			log.debug(map);
			return this.queryForList(sql, map);
		} else {
			return selectLogicPageList(  sql, map,   offset,   limit);
		}
		
	}

	/**
	 * @param sqlKey
	 * @param map
	 * @param offset 
	 * @param limit
	 * @return  offset+1~offset+limit
	 * @throws SQLException
	 */
	public List<Map<String, Object>> selectLogicPageList(String sql,
			Map<String, Object> map, int offset, int limit)   {
		log.debug("offset " + offset + " limit " + limit);
		log.debug(sql);
		List<Map<String, Object>> rsList= new ArrayList<Map<String, Object>>(limit);
		List<Map<String, Object>> ls = this.queryForList(sql, map);
		// offset+1~offset+limit
		for (int rownum = 0 ; rownum <ls.size()&&rownum <= offset+limit  ;rownum++){
			log.debug(ls.get(rownum));
			if(rownum >= offset+1 && rownum <= offset+limit ){
				log.debug("add ...." + ls.get(rownum));
				rsList.add(ls.get(rownum));
			}
		}
		return rsList;
	}

	

	/**
	 * 
	 * @param mapperId
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> selectPageList(String mapperId,
			Map<String, Object> map) {
		ResultMap<String, Object> rm = new ResultMap<String, Object>(map);
		int offset = rm.getInt(Dialect.OFFSET);
		int limit = rm.hasKey(Dialect.LIMIT) ? rm.getInt(Dialect.LIMIT)
				: DEFAULT_PAGE_SIZE;
		return this.selectPageList(mapperId, map, offset, limit);
	}

	/**
	 * 
	 * @param mapperId
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public Long selectCount(String sqlKey, Map<String, Object> map) {
		String sql = dialect.getCountString(sqlTemplate.getSql(sqlKey, map));
		return this.queryForObject(sql, map, Long.class);
	}

	/**
	 * sql example: update table set colname = '' where user_id = :userId
	 * 
	 * @param sqlKey
	 * @param map
	 */
	public int updateBySqlKey(String sqlKey, Map<String, Object> map) {
		String sql = sqlTemplate.getSql(sqlKey, map);
		return this.update(sql, map);
	}

	public String getSql(String sqlKey, Map<String, Object> map) {
			String sql = sqlTemplate.getSql(sqlKey, map);
			return "[" + sqlKey + "]" + getExecuteSql(sql,map);
	}
	
	public String getExecuteSql(String sql, Map<String, Object> map) {
		String convertSql = sqlConvert(sql, map);
		ParsedSql parsedSql = getParsedSql(sql);
		MapSqlParameterSource paramSource = new MapSqlParameterSource(map) ;
		String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource);
		Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource,null) ;
		List<Object> paramList = Arrays.asList(params);
		String executeSql =  getSql(sqlToUse,paramList) ;
		log.debug("sql :" + sql);
		log.debug("convertSql :" + convertSql);
		log.debug("parsedSql :" + parsedSql);
		log.debug("sqlToUse :" + sqlToUse);
		log.debug("params :" + paramList );
		log.debug("executeSql :" + executeSql);
		return  executeSql;
	}
	
	/**
	 * sql ( select * from table where user_id = :userId ) Convert to standard
	 * jdbc sql ( select * from table where user_id = ?) ;
	 * 
	 * @param sql
	 * @param map
	 * @param list
	 * @return
	 */
	@SuppressWarnings("all")
	private String sqlConvert(String sql, Map qryMap) {
		// replace #param to value
		if (qryMap != null && !qryMap.isEmpty()) {
			List<String> params = getReplaceParameterList(sql);
			for (String param : params) {
				sql = StringUtils.replaceOnce(sql, "#" + param, StringUtil.asString( qryMap.get(param)));
			}
		}
		return sql;
	}

	
	private String getSql(String sql, List<Object> lstParam) {
		for (int i = 0; i < lstParam.size(); i++) {
			Object value = lstParam.get(i);
			if (value instanceof Integer || value instanceof Double
					|| value instanceof Float || value instanceof Long) {
				sql = StringUtils.replaceOnce(sql, "?", value.toString());
			} else if (value instanceof String) {
				sql = StringUtils.replaceOnce(sql, "?", "'" + value.toString()
						+ "'");
			}  else {
				sql = StringUtils.replaceOnce(sql, "?",
						"'" + String.valueOf(value) + "'");
			}
		}
		return sql;
	}

	private String getCacheKey(String sqlKey, Map<String, Object> qryMap) {
		StringBuffer cacheKey = new StringBuffer(sqlKey);
		if (qryMap != null && !qryMap.isEmpty())  
			cacheKey.append(cacheKey.toString()).append("_").append(qryMap.toString());
		return cacheKey.toString();
	}
	
	/**
	 * sql ( select * from table where user_id = #userId ) #userId will be replaced
	 * into list as param
	 * 
	 * @param sql
	 * @return
	 */
	private List<String> getReplaceParameterList(String sql) {
		String regex = "\\#(\\w+)";
		Pattern pattern = Pattern.compile(regex);
		Matcher matcher = pattern.matcher(sql);
		List<String> params = new ArrayList<String>();
		while (matcher.find())
			params.add(matcher.group(1));
		return params;
	}  



	public void setDataCache(Cache dataCache) {
		log.debug("setDataCache" +dataCache);
		this.dataCache = dataCache;
	}

	public void setSqlTemplate(SqlTemplate sqlTemplate) {
		this.sqlTemplate = sqlTemplate;
	}
	
	public void setDialect(Dialect dialect) {
		this.dialect = dialect;
	}


}
